{smcl}
{com}{sf}{ul off}{txt}{.-}
      name:  {res}<unnamed>
       {txt}log:  {res}C:\Users\hilar\Box\lights_2022\replication package\code\read_pfaf4char.smcl
  {txt}log type:  {res}smcl
 {txt}opened on:  {res}11 Dec 2025, 17:14:15
{txt}
{com}. timer on 1
{txt}
{com}. clear
{txt}
{com}. 
. 
. 
. * data from GIS "spatial join" of dams to pfaf4 codes
. import delimited "../data/grand_pfaf4_global.txt", clear
{res}{txt}(encoding automatically selected: ISO-8859-1)
{text}(68 vars, 6,862 obs)

{com}. 
. 
. **# Generate dam counts
. 
. *first, gen separate vars for diff dam types
. *will =0 for for missing uses
. 
. gen irrig=(use_irr=="Main" | use_irr=="Major")
{txt}
{com}. gen hydro=(use_elec=="Main" | use_elec=="Major")
{txt}
{com}. gen supply=(use_supp=="Main" | use_supp=="Major")
{txt}
{com}. gen flood=(use_fcon=="Main" | use_fcon=="Major")
{txt}
{com}. 
. 
. sort grand_id
{txt}
{com}. 
. *clean/prep grand variables for collapsing by means/sums
. *grand missing numerics are coded to -99
. replace cap_mcm=. if cap_mcm<0
{txt}(8 real changes made, 8 to missing)

{com}. replace cap_rep=. if cap_rep<0
{txt}(540 real changes made, 540 to missing)

{com}. replace area_skm=. if area_skm<0
{txt}(21 real changes made, 21 to missing)

{com}. replace dor_pc=. if dor_pc<0
{txt}(8 real changes made, 8 to missing)

{com}. replace year=. if year<0
{txt}(408 real changes made, 408 to missing)

{com}. replace dam_hgt_m=. if dam_hgt_m<0
{txt}(472 real changes made, 472 to missing)

{com}. *6.88% of dams (472) missing height, only 8 dams missing cap_mcm 
. 
. *only dams with capacity>=.1km3 are necessarily included in GRanD 
. * variable to restrict attention to these
. * capacity in million m3 
. gen largedam=(cap_mcm>=100) & !missing(cap_mcm)
{txt}
{com}. gen largeirsuhy=largedam*(irrig | supply | hydro)
{txt}
{com}. gen irsuhy=(irrig | supply | hydro)
{txt}
{com}. 
. *reservoir capacity for only hydro dams
. gen cap_mcm_hydro=cap_mcm*hydro
{txt}(8 missing values generated)

{com}. 
. 
. sort conpfaf4 
{txt}
{com}. 
. #delimit ;
{txt}delimiter now ;
{com}. collapse (sum) count_irr=irrig count_flood=flood count_supply=supply count_hydro=hydro 
> count_large=largedam rescap=cap_mcm rescaphydro=cap_mcm_hydro resarea=area_skm height=dam_hgt_m 
> (count) count_dams=grand_id , by(conpfaf4) ;
{res}{txt}
{com}. #delimit cr 
{txt}delimiter now cr
{com}. 
. label var resarea "Reservoir area (sq km)"
{txt}
{com}. 
. * 3 dams outside of any pfaf4, drop them
. drop if conpfaf4<10000
{txt}(1 observation deleted)

{com}. 
. sort conpfaf4
{txt}
{com}. 
. 
. tempfile dams
{txt}
{com}. save `dams'
{txt}{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_000001.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. 
. **# Other pfaf4 charactertics from export of Hydro1k shapefile attribute table
. import delimited "../data/pfaf4char_global.txt", clear
{res}{txt}(encoding automatically selected: ISO-8859-2)
{res}{text}(11 vars, 13,977 obs)

{com}. 
. rename level4 pfaf4
{res}{txt}
{com}. rename mean_slope_mean slope
{res}{txt}
{com}. rename max_slope_mean max_slope
{res}{txt}
{com}. rename shape_area pfaf4area
{res}{txt}
{com}. rename mean_cti_mean cti
{res}{txt}
{com}. rename mean_aspect_mea aspect
{res}{txt}
{com}. drop objectid shape_length mean_dem_mean
{txt}
{com}. 
. 
. label var slope "Slope (mean) in subbasin"
{txt}
{com}. label var max_slope "Max slope in subbasin"
{txt}
{com}. label var cti "Wetness index"
{txt}
{com}. label var aspect "Aspect"
{txt}
{com}. label var pfaf4area "Subbasin area"
{txt}
{com}. 
. drop if pfaf4<0
{txt}(7 observations deleted)

{com}. 
. sort conpfaf4
{txt}
{com}. 
. 
. merge 1:1 conpfaf4 using `dams'
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}          12,126
{txt}{col 9}from master{col 30}{res}          12,125{txt}  (_merge==1)
{col 9}from using{col 30}{res}               1{txt}  (_merge==2)

{col 5}Matched{col 30}{res}           1,845{txt}  (_merge==3)
{col 5}{hline 41}

{com}. 
. * if missing from dams data, set dams count to zero
. foreach var of varlist  count_* rescap rescaphydro resarea height {c -(}
{txt}  2{com}.    replace `var'= 0  if _merge==1
{txt}  3{com}.    local nocount=substr("`var'",max(strpos("`var'","_")+1,1),.)
{txt}  4{com}.    *generate density_`nocount' = `var'/pfaf4area
.  {c )-}
{txt}(12,125 real changes made)
(12,125 real changes made)
(12,125 real changes made)
(12,125 real changes made)
(12,125 real changes made)
(12,125 real changes made)
(12,125 real changes made)
(12,125 real changes made)
(12,125 real changes made)
(12,125 real changes made)

{com}. 
. 
. drop if _merge==2
{txt}(1 observation deleted)

{com}. drop _merge
{txt}
{com}. 
. gen ifdam=count_dams>0
{txt}
{com}. gen ifhydro=count_hydro>0
{txt}
{com}. 
. label var count_dams "Total dams (#)"
{txt}
{com}. label var count_hydro "Hydro dams (#)"
{txt}
{com}. label var count_supply "Supply dams (#)"
{txt}
{com}. label var count_irr "Irrigation dams (#)"
{txt}
{com}. label var count_large "Large dams (#)"
{txt}
{com}. label var count_flood "Flood control dams (#)"
{txt}
{com}. label var ifdam "Dam" 
{txt}
{com}. label var ifhydro "Hydro dam"
{txt}
{com}. label var height "Dam height"
{txt}
{com}. label var rescap "Total reservoir capacity (mcm)"
{txt}
{com}. label var rescaphydro "Hydro reservoir capacity (mcm)"
{txt}
{com}. 
. label define contlabel 1 "AF" 2 "AS" 3 "AU" 4 "EU" 5 "NA" 6 "SA"
{txt}
{com}. 
. label value continent contlabel
{txt}
{com}. label var conpfaf4 "Pfaf4, unique by continent" 
{txt}
{com}. 
. 
. sort conpfaf4
{txt}
{com}. 
. tempfile damsplus
{txt}
{com}. 
. save `damsplus'
{txt}{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_000002.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. 
. *Population by pfaf4 from ArcMap
. import delimited "../data/pop2000_pfaf4.txt", clear
{res}{txt}(encoding automatically selected: ISO-8859-2)
{res}{text}(10 vars, 13,902 obs)

{com}. 
. rename mean pop2000
{res}{txt}
{com}. 
. label var pop2000 "Average population density in 2000 in pfaf4"
{txt}
{com}. 
. keep conpfaf4 pop2000
{txt}
{com}. 
. sort conpfaf4
{txt}
{com}. 
. 
. merge 1:1 conpfaf4 using `damsplus'
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}              80
{txt}{col 9}from master{col 30}{res}               6{txt}  (_merge==1)
{col 9}from using{col 30}{res}              74{txt}  (_merge==2)

{col 5}Matched{col 30}{res}          13,896{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop if _merge==2
{txt}(74 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. tempfile pfaf4
{txt}
{com}. 
. save `pfaf4'
{txt}{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_000003.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. **# Upstream basin info
. 
. foreach cont in Africa Asia Australasia Europe North_America South_America {c -(}
{txt}  2{com}. import excel using "../data/level4_up_basins/`cont'_Level_4UpBasins.xlsx", sheet("Sheet1") firstrow case(lower) clear
{txt}  3{com}. drop fid objectid
{txt}  4{com}. drop if level4<0
{txt}  5{com}. reshape long up, i(level4) j(j)
{txt}  6{com}. drop if up==.
{txt}  7{com}. tempfile `cont'_up
{txt}  8{com}. save ``cont'_up'
{txt}  9{com}. {c )-}
{res}{text}(397 vars, 3,355 obs)
(1 observation deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}       3,354   {txt}->   {res}1,314,768   
{txt}Number of variables        {res}         395   {txt}->   {res}5           
{txt}j variable (392 values)                   ->   {res}j
{txt}xij variables:
                      {res}up1 up2 ... up392   {txt}->   {res}up
{txt}{hline 77}
(1,270,142 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_000004.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(413 vars, 3,423 obs)
(1 observation deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}       3,422   {txt}->   {res}1,396,176   
{txt}Number of variables        {res}         411   {txt}->   {res}5           
{txt}j variable (408 values)                   ->   {res}j
{txt}xij variables:
                      {res}up1 up2 ... up408   {txt}->   {res}up
{txt}{hline 77}
(1,345,562 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_000005.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(29 vars, 384 obs)
(2 observations deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}         382   {txt}->   {res}9,168       
{txt}Number of variables        {res}          27   {txt}->   {res}5           
{txt}j variable (24 values)                    ->   {res}j
{txt}xij variables:
                       {res}up1 up2 ... up24   {txt}->   {res}up
{txt}{hline 77}
(8,600 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_000006.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(225 vars, 1,809 obs)
(1 observation deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}       1,808   {txt}->   {res}397,760     
{txt}Number of variables        {res}         223   {txt}->   {res}5           
{txt}j variable (220 values)                   ->   {res}j
{txt}xij variables:
                      {res}up1 up2 ... up220   {txt}->   {res}up
{txt}{hline 77}
(383,094 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_000007.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(349 vars, 3,231 obs)
(1 observation deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}       3,230   {txt}->   {res}1,111,120   
{txt}Number of variables        {res}         347   {txt}->   {res}5           
{txt}j variable (344 values)                   ->   {res}j
{txt}xij variables:
                      {res}up1 up2 ... up344   {txt}->   {res}up
{txt}{hline 77}
(1,078,422 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_000008.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(213 vars, 1,775 obs)
(1 observation deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}       1,774   {txt}->   {res}368,992     
{txt}Number of variables        {res}         211   {txt}->   {res}5           
{txt}j variable (208 values)                   ->   {res}j
{txt}xij variables:
                      {res}up1 up2 ... up208   {txt}->   {res}up
{txt}{hline 77}
(357,182 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_000009.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. use `South_America_up'
{txt}
{com}. 
. append using `Africa_up' `Asia_up' `Australasia_up' `Europe_up' `North_America_up'
{txt}
{com}. 
. replace up=continent*10000+up
{txt}variable {bf}{res}up{sf}{txt} was {bf}{res}int{sf}{txt} now {bf}{res}long{sf}
{txt}(154,982 real changes made)

{com}. 
. rename j upstream_count 
{res}{txt}
{com}. 
. label var upstream_count "How many basins upstream"
{txt}
{com}. 
. * save this file because needed later to merge upstream droughts
. save "../data/upstream_basins.dta", replace
{txt}{p 0 4 2}
file {bf}
../data/upstream_basins.dta{rm}
saved
{p_end}

{com}. 
. **# Add characteristics of upstream basins
. 
. use "../data/upstream_basins.dta", clear
{txt}
{com}. * change names to merge on characteristics of upstream basins, not this basin
. rename conpfaf4 start_basin
{res}{txt}
{com}. rename up conpfaf4
{res}{txt}
{com}. 
. sort conpfaf4
{txt}
{com}. 
. *merge to dam data by pfaf4 for upstream basin
. merge m:1 conpfaf4 using `damsplus', keep(match)
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}               0
{txt}{col 5}Matched{col 30}{res}         154,982{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop _merge
{txt}
{com}. 
. sort start_basin
{txt}
{com}. * save for use in closest upstream basin
. 
. tempfile up
{txt}
{com}. save `up'
{txt}{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_00000a.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. collapse (sum) upstream_dams=count_dams upstream_hydro=count_hydro  ///
> upstream_irr=count_irr upstream_flood=count_flood upstream_supply=count_supply ///
> upstream_height=height upstream_rescap=rescap upstream_area=pfaf4area ///
> (max) upstream_subbasins=upstream_count, by(start_basin)
{res}{txt}
{com}. 
. rename start_basin conpfaf4
{res}{txt}
{com}. 
. foreach type in hydro irr flood supply {c -(}
{txt}  2{com}.         label var upstream_`type'  "Upstream `type' dams (#)"
{txt}  3{com}. {c )-}
{txt}
{com}. 
. label var upstream_dams "Upstream all dams (#)"
{txt}
{com}. label var upstream_height "Total height of dams upstream"
{txt}
{com}. label var upstream_area "Total upstream basin area"
{txt}
{com}. label var upstream_subbasins "Count of upstream subbasins"
{txt}
{com}. label var upstream_rescap "Total upstream reservoir capacity"
{txt}
{com}. 
. sort conpfaf4
{txt}
{com}. 
. tempfile all_up
{txt}
{com}. 
. save `all_up'
{txt}{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_00000b.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. *just characteristics of the nearest upstream basin
. 
. use `up', clear
{txt}
{com}. keep if upstream_count==1
{txt}(149,424 observations deleted)

{com}. 
. drop conpfaf4
{txt}
{com}. rename start_basin conpfaf4
{res}{txt}
{com}. foreach var in ifdam ifhydro height rescap count_dams count_hydro pfaf4area {c -(}
{txt}  2{com}.         local lbl : variable label `var'
{txt}  3{com}.         rename `var' up1_`var'
{txt}  4{com}.         label var up1_`var'  "Near upstream `lbl'"
{txt}  5{com}. {c )-}
{res}{txt}
{com}. 
. 
. keep conpfaf4 up1_*
{txt}
{com}. 
. sort conpfaf4
{txt}
{com}. 
. merge 1:1 conpfaf4 using `all_up'
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}               0
{txt}{col 5}Matched{col 30}{res}           5,558{txt}  (_merge==3)
{col 5}{hline 41}

{com}. drop _merge
{txt}
{com}. 
. 
. merge 1:1 conpfaf4 using `pfaf4'
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}           8,472
{txt}{col 9}from master{col 30}{res}              64{txt}  (_merge==1)
{col 9}from using{col 30}{res}           8,408{txt}  (_merge==2)

{col 5}Matched{col 30}{res}           5,494{txt}  (_merge==3)
{col 5}{hline 41}

{com}. gen no_upstream_subbasin=(_merge==2)
{txt}
{com}. drop if _merge==1
{txt}(64 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. label var no_upstream_subbasin "Pfaf4 with no upstream basins"
{txt}
{com}. 
. tempfile own_up
{txt}
{com}. 
. save `own_up'
{txt}{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_00000c.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. **# Downstream country characteristics
. ** used for IVs
. 
. * list of countries for each pfaf4
. *result of "Union" command between pfaf4 and country shapefiles in GIS
. import delimited "../data/pfaf4_country_match.txt", clear
{res}{txt}(encoding automatically selected: ISO-8859-1)
{res}{text}(20 vars, 14,268 obs)

{com}. 
. keep conpfaf4 iso_cc
{txt}
{com}. duplicates drop

{p 0 4}{txt}Duplicates in terms of {txt} all variables{p_end}

(287 observations deleted)

{com}. sort conpfaf4
{txt}
{com}. 
. * because a pfaf4 may have multiple countries need one listing per pfaf-country pair
. by conpfaf4: gen ci=_n
{txt}
{com}. by conpfaf4: egen numcountry=max(ci)
{txt}
{com}. 
. reshape wide iso_cc, i(conpfaf4 numcountry) j(ci)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12)

Data{col 36}Long{col 43}->{col 48}Wide
{hline 77}
Number of observations     {res}      13,981   {txt}->   {res}12,728      
{txt}Number of variables        {res}           4   {txt}->   {res}14          
{txt}j variable (12 values)               {res}ci   {txt}->   (dropped)
xij variables:
                                 {res}iso_cc   {txt}->   {res}iso_cc1 iso_cc2 ... iso_cc12
{txt}{hline 77}

{com}. 
. tempfile pfaf4_country
{txt}
{com}. 
. save `pfaf4_country'
{txt}{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_00000d.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. **# Downstream basin info
. 
. 
. 
. foreach cont in Africa Asia Australasia Europe North_America South_America {c -(}
{txt}  2{com}. import excel using "../data/level4_down_basins/`cont'_Level_4DownBasins.xlsx", sheet("Sheet1") firstrow case(lower) clear
{txt}  3{com}. drop if start_basin<0
{txt}  4{com}. drop fid objectid start_basin
{txt}  5{com}. reshape long down, i(conpfaf4) j(j)
{txt}  6{com}. replace down=. if down<0 
{txt}  7{com}. drop if down==.
{txt}  8{com}. tempfile `cont'_down
{txt}  9{com}. save ``cont'_down'
{txt} 10{com}. {c )-}
{res}{text}(61 vars, 3,355 obs)
(1 observation deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}       3,354   {txt}->   {res}187,824     
{txt}Number of variables        {res}          58   {txt}->   {res}4           
{txt}j variable (56 values)                    ->   {res}j
{txt}xij variables:
                 {res}down1 down2 ... down56   {txt}->   {res}down
{txt}{hline 77}
(143,198 real changes made, 143,198 to missing)
(143,198 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_00000e.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(65 vars, 3,423 obs)
(1 observation deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}       3,422   {txt}->   {res}205,320     
{txt}Number of variables        {res}          62   {txt}->   {res}4           
{txt}j variable (60 values)                    ->   {res}j
{txt}xij variables:
                 {res}down1 down2 ... down60   {txt}->   {res}down
{txt}{hline 77}
(154,706 real changes made, 154,706 to missing)
(154,706 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_00000f.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(13 vars, 384 obs)
(2 observations deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}         382   {txt}->   {res}3,056       
{txt}Number of variables        {res}          10   {txt}->   {res}4           
{txt}j variable (8 values)                     ->   {res}j
{txt}xij variables:
                  {res}down1 down2 ... down8   {txt}->   {res}down
{txt}{hline 77}
(2,488 real changes made, 2,488 to missing)
(2,488 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_00000g.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(45 vars, 1,809 obs)
(1 observation deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}       1,808   {txt}->   {res}72,320      
{txt}Number of variables        {res}          42   {txt}->   {res}4           
{txt}j variable (40 values)                    ->   {res}j
{txt}xij variables:
                 {res}down1 down2 ... down40   {txt}->   {res}down
{txt}{hline 77}
(58,084 real changes made, 58,084 to missing)
(58,084 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_00000h.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(52 vars, 3,231 obs)
(1 observation deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}       3,230   {txt}->   {res}151,810     
{txt}Number of variables        {res}          49   {txt}->   {res}4           
{txt}j variable (47 values)                    ->   {res}j
{txt}xij variables:
                 {res}down1 down2 ... down47   {txt}->   {res}down
{txt}{hline 77}
(119,112 real changes made, 119,112 to missing)
(119,112 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_00000i.tmp{rm}
saved
as .dta format
{p_end}
{res}{text}(37 vars, 1,775 obs)
(1 observation deleted)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}       1,774   {txt}->   {res}56,768      
{txt}Number of variables        {res}          34   {txt}->   {res}4           
{txt}j variable (32 values)                    ->   {res}j
{txt}xij variables:
                 {res}down1 down2 ... down32   {txt}->   {res}down
{txt}{hline 77}
(44,918 real changes made, 44,918 to missing)
(44,918 observations deleted)
{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_00000j.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. append using `Africa_down' `Asia_down' `Australasia_down' `Europe_down' `North_America_down'
{txt}
{com}. 
. rename conpfaf4 start_basin
{res}{txt}
{com}. 
. replace down=continent*10000+down
{txt}variable {bf}{res}down{sf}{txt} was {bf}{res}int{sf}{txt} now {bf}{res}long{sf}
{txt}(154,592 real changes made)

{com}. 
. bysort start_basin: egen dncount=max(j)
{txt}
{com}. drop j
{txt}
{com}. 
. * now merge downstream basins to country IDs
. rename down conpfaf4
{res}{txt}
{com}. sort conpfaf4
{txt}
{com}. 
. merge m:1 conpfaf4 using `pfaf4_country'
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}          36,923
{txt}{col 9}from master{col 30}{res}          28,747{txt}  (_merge==1)
{col 9}from using{col 30}{res}           8,176{txt}  (_merge==2)

{col 5}Matched{col 30}{res}         125,845{txt}  (_merge==3)
{col 5}{hline 41}

{com}. *** need to drop basins that don't appear downstream of anyone
. drop if _merge==2
{txt}(8,176 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. * this data by downstream pfaf, now need to collect all downstream countries
. * into single record for the start_basin
. rename conpfaf4 dnpfaf4
{res}{txt}
{com}. sort start_basin
{txt}
{com}. 
. * first reshape to 1 dnbasin 1 obs format
. gen ID = _n
{txt}
{com}. reshape long iso_cc, i(ID) j(num)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10 11 12)

Data{col 36}Wide{col 43}->{col 48}Long
{hline 77}
Number of observations     {res}     154,592   {txt}->   {res}1,855,104   
{txt}Number of variables        {res}          18   {txt}->   {res}8           
{txt}j variable (12 values)                    ->   {res}num
{txt}xij variables:
           {res}iso_cc1 iso_cc2 ... iso_cc12   {txt}->   {res}iso_cc
{txt}{hline 77}

{com}. drop if missing(iso_cc)
{txt}(1,712,765 observations deleted)

{com}. 
. * get rid of same country in different downstream subbasin
. duplicates drop start_basin iso_cc, force

{p 0 4}{txt}Duplicates in terms of {res} start_basin iso_cc{p_end}

{txt}(123,607 observations deleted)

{com}. 
. bysort start_basin: replace num = _n
{txt}(5,651 real changes made)

{com}. by start_basin: egen dncountry_count=max(num)
{txt}
{com}. 
. replace dncountry_count=dncountry_count-1 if dncountry_count>0
{txt}(18,732 real changes made)

{com}. 
. rename iso_cc dncountry_iso
{res}{txt}
{com}. 
. drop ID numcountry dnpfaf4
{txt}
{com}. reshape wide dncountry_iso, i(start_basin dncountry_count) j(num)
{res}{txt}(j = 1 2 3 4 5 6 7 8 9 10)

Data{col 36}Long{col 43}->{col 48}Wide
{hline 77}
Number of observations     {res}      18,732   {txt}->   {res}10,979      
{txt}Number of variables        {res}           6   {txt}->   {res}14          
{txt}j variable (10 values)              {res}num   {txt}->   (dropped)
xij variables:
                          {res}dncountry_iso   {txt}->   {res}dncountry_iso1 dncountry_iso2 ... dncountry_iso10
{txt}{hline 77}

{com}. 
. *** remerge with original country codes to check and add back in obs with no downbasin
. rename start_basin conpfaf4
{res}{txt}
{com}. sort conpfaf4
{txt}
{com}. 
. merge 1:1 conpfaf4 using `pfaf4_country'
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}           3,683
{txt}{col 9}from master{col 30}{res}             967{txt}  (_merge==1)
{col 9}from using{col 30}{res}           2,716{txt}  (_merge==2)

{col 5}Matched{col 30}{res}          10,012{txt}  (_merge==3)
{col 5}{hline 41}

{com}. 
. replace dncountry_count=0 if _merge==2 
{txt}(2,716 real changes made)

{com}. 
. drop _merge
{txt}
{com}. 
. label var numcountry "Num. countries in this pfaf4"
{txt}
{com}. label var dncountry_count "Num. foreign downstream countries" 
{txt}
{com}. 
. rename iso_cc* country*
{res}{txt}
{com}. 
. sort conpfaf4
{txt}
{com}. 
. tempfile dn_country
{txt}
{com}. save `dn_country'
{txt}{p 0 4 2}
file {bf}
C:\Users\hilar\AppData\Local\Temp\ST_5794_00000k.tmp{rm}
saved
as .dta format
{p_end}

{com}. 
. use `own_up', clear
{txt}
{com}. 
. merge 1:1 conpfaf4 using `dn_country', keepusing(dncountry_count numcountry country1 country2)
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}             333
{txt}{col 9}from master{col 30}{res}             270{txt}  (_merge==1)
{col 9}from using{col 30}{res}              63{txt}  (_merge==2)

{col 5}Matched{col 30}{res}          13,632{txt}  (_merge==3)
{col 5}{hline 41}

{com}. 
. rename country1 country
{res}{txt}
{com}. label var country "Country (ISO code)"
{txt}
{com}. label var country2 "Second country ISO code, if present"
{txt}
{com}. 
. drop if _merge==2
{txt}(63 observations deleted)

{com}. drop _merge
{txt}
{com}. 
. 
. compress
  {txt}variable {bf}{res}up1_count_dams{sf}{txt} was {bf}{res}long{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}up1_ifdam{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}up1_ifhydro{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}count_dams{sf}{txt} was {bf}{res}long{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}{res}ifdam{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}ifhydro{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}no_upstream_subbasin{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}dncountry_count{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}numcountry{sf}{txt} was {bf}{res}float{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}up1_count_hydro{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}up1_height{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}{res}upstream_dams{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}{res}upstream_hydro{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}upstream_irr{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}{res}upstream_flood{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}{res}upstream_supply{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}{res}upstream_height{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}{res}count_irr{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}int{sf}
  {txt}variable {bf}{res}count_flood{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}count_supply{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}count_hydro{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}count_large{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}byte{sf}
  {txt}variable {bf}{res}height{sf}{txt} was {bf}{res}double{sf}{txt} now {bf}{res}int{sf}
{txt}  (1,612,632 bytes saved)

{com}. 
. save "../data/pfaf4_char.dta", replace
{txt}{p 0 4 2}
file {bf}
../data/pfaf4_char.dta{rm}
saved
{p_end}

{com}. 
. timer off 1
{txt}
{com}. timer list 1
{res}   1:      6.07 /        1 =       6.0740
{txt}
{com}. 
. 
. log close
      {txt}name:  {res}<unnamed>
       {txt}log:  {res}C:\Users\hilar\Box\lights_2022\replication package\code\read_pfaf4char.smcl
  {txt}log type:  {res}smcl
 {txt}closed on:  {res}11 Dec 2025, 17:14:21
{txt}{.-}
{smcl}
{txt}{sf}{ul off}